﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
namespace WPFExcelReport
{
    public partial class VGSMATK
    {

        public void SetUpNewRow(ref DataRow dwReturn)
        {

            switch (app.company)
            {
                default:
                case "YGR":
                    #region=========
                    dwReturn["GSMMANDANT"] = app.Mandant;
                    dwReturn["GSMART"] = "                    ";
                    dwReturn["GSMVAR"] = "000";
                    dwReturn["GSMPOS"] = "00";
                    dwReturn["GSMRBEZ"] = "      ";
                    dwReturn["GSMRFARBE"] = "          ";
                    dwReturn["GSMRANT"] = "1000";
                    dwReturn["GSMSTAPELV"] = "0";
                    dwReturn["GSMSTAPELB"] = "0";
                    dwReturn["GSMDTEX"] = "0";
                    dwReturn["GSMBON"] = " ";
                    dwReturn["GSMRFBBEZ"] = "          ";
                    dwReturn["GSMABF"] = "0";
                    dwReturn["GSMKGNET"] = "0";
                    dwReturn["GSMKGBRUT"] = "0";
                    dwReturn["GSMDISPOKZ"] = " ";
                    dwReturn["GSMRSPZ"] = "                         ";
                    dwReturn["GSMRKZ"] = " ";
                    dwReturn["GSMLIEFNR"] = "        ";
                    dwReturn["GSMVERG"] = "0";
                    dwReturn["GSMVEART1"] = "   ";
                    dwReturn["GSMVESATZ1"] = "0";
                    dwReturn["GSMVEART2"] = "   ";
                    dwReturn["GSMVESATZ2"] = "0";
                    dwReturn["GSMFILLER"] = "                       ";
                    #endregion
                    break;
            }
        }
        public void InsertGSMATK(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO GSMATK ( GSMMANDANT,GSMART,GSMVAR,GSMPOS,GSMRBEZ,GSMRFARBE,GSMRANT,GSMSTAPELV,GSMSTAPELB,GSMDTEX,GSMBON,GSMRFBBEZ,GSMABF,GSMKGNET,GSMKGBRUT,GSMDISPOKZ,GSMRSPZ,GSMRKZ,GSMLIEFNR,GSMVERG,GSMVEART1,GSMVESATZ1,GSMVEART2,GSMVESATZ2,GSMFILLER)"
                   + " VALUES (:GSMMANDANT:,:GSMART:,:GSMVAR:,:GSMPOS:,:GSMRBEZ:,:GSMRFARBE:,:GSMRANT:,:GSMSTAPELV:,:GSMSTAPELB:,:GSMDTEX:,:GSMBON:,:GSMRFBBEZ:,:GSMABF:,:GSMKGNET:,:GSMKGBRUT:,:GSMDISPOKZ:,:GSMRSPZ:,:GSMRKZ:,:GSMLIEFNR:,:GSMVERG:,:GSMVEART1:,:GSMVESATZ1:,:GSMVEART2:,:GSMVESATZ2:,:GSMFILLER:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }
            Connect2Proxy(or1);
        }

        public override string BATCHINSERT(DataRow dwData)
        {
            string value = "";
            switch (app.company)
            {
                default:     
                case "YGR":
                case "TST":
                case "SXN":
                    value = "INTO GSMATK ( GSMMANDANT,GSMART,GSMVAR,GSMPOS,GSMRBEZ,GSMRFARBE,GSMRANT,GSMSTAPELV,GSMSTAPELB,GSMDTEX,GSMBON,GSMRFBBEZ,GSMABF,GSMKGNET,GSMKGBRUT,GSMDISPOKZ,GSMRSPZ,GSMRKZ,GSMLIEFNR,GSMVERG,GSMVEART1,GSMVESATZ1,GSMVEART2,GSMVESATZ2,GSMFILLER)"
                    + " VALUES (:GSMMANDANT:,:GSMART:,:GSMVAR:,:GSMPOS:,:GSMRBEZ:,:GSMRFARBE:,:GSMRANT:,:GSMSTAPELV:,:GSMSTAPELB:,:GSMDTEX:,:GSMBON:,:GSMRFBBEZ:,:GSMABF:,:GSMKGNET:,:GSMKGBRUT:,:GSMDISPOKZ:,:GSMRSPZ:,:GSMRKZ:,:GSMLIEFNR:,:GSMVERG:,:GSMVEART1:,:GSMVESATZ1:,:GSMVEART2:,:GSMVESATZ2:,:GSMFILLER:)";
                    value = INTEXDBC.InjectData(value, dwData);
                    break;
            }
            return value;
        }
        public DataSet QueryGSMATK(DataRow dwData, int option = 1)
        {
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM GSMATK WHERE  ROWNUM<2 ";
                    break;
                case 1:
                    or1.CommandText = "SELECT * FROM GSMATK WHERE  "
                    + "  GSMMANDANT=:GSMMANDANT: AND GSMART=:GSMART: AND GSMVAR=:GSMVAR:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 2:
                    or1.CommandText = "SELECT * FROM GSMATK WHERE  "
                    + "  GSMMANDANT=:GSMMANDANT: AND GSMART=:GSMART: AND GSMVAR=:GSMVAR: AND GSMPOS=:GSMPOS:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 3:
                    or1.CommandText = "SELECT * FROM GSMATK WHERE  "
                    + "  GSMMANDANT=:GSMMANDANT: AND GSMART=:GSMART: AND GSMVAR=:GSMVAR: AND GSMRFARBE=:GSMRFARBE:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 4:
                    or1.CommandText = "SELECT DISTINCT GS.* FROM GAAUF GA RIGHT JOIN GSMATK GS ON GAMANDANT=GSMMANDANT"
                        + " AND GAART=GSMART "
                        + "  WHERE GAMANDANT=:GAMANDANT: AND  GAAUFKRS=:GAAUFKRS: AND GAAUFLFD=:GAAUFLFD: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 5:
                    or1.CommandText = "SELECT DISTINCT '            ' AS COMP,GSMVAR,VAFKOLL,VAFART,VAFFARBE,VAKBKUNDNR"
                    +   " FROM VAART JOIN VAFARBEN  ON VAAMANDANT=VAFMANDANT AND VAAKOLL=VAFKOLL AND VAAART=VAFART"
                        + " JOIN VAKDBEZ ON VAKBMANDANT=VAFMANDANT AND VAKBKOLL=VAFKOLL AND VAKBART=VAFART AND VAKBFARBE=VAFFARBE"
                        + " JOIN GSMATK ON GSMMANDANT=VAFMANDANT AND SUBSTR(GSMART,1,10)=VAFFARBE  "
                        + "  WHERE VAAMANDANT=:VAAMANDANT: AND  VAAKOLL=:VAAKOLL: AND VAAART=:VAAART: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 6:
                    or1.CommandText = "SELECT * FROM GSMATK WHERE  "
                    + "  GSMMANDANT=:GSMMANDANT: AND GSMART=:GSMART: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 99:
                    or1.CommandText = "SELECT * FROM GSMATK";
                    break;
                default:
                    break;
            }
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;

            return SResult;
        }
        public void DeleteGSMATK(DataRow dwData, int option = 1)
        {

            or1 = OracleDB.CreateCommand();
             switch (option)
            {
                case 1:
                    DGSM01(dwData);
                    break;
                 case 2:
                    DGSM02(dwData);
                    break;
                default:
                    break;
            }
            
            Connect2Proxy(or1);
        }
        public void DGSM01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM GSMATK  WHERE  GSMMANDANT=:GSMMANDANT: AND GSMART=:GSMART: AND GSMVAR=:GSMVAR: AND GSMPOS=:GSMPOS:"; 
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void DGSM02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM GSMATK  WHERE  GSMMANDANT=:GSMMANDANT: AND GSMART=:GSMART:"; 
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void UpdateGSMATK(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            if (option == 0)
            {
                switch (app.company)
                {
                    default:
                    case "YGR":
                        option = 1;
                        break;
                }
            }
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "UPDATE GSMATK SET GSMMANDANT=:GSMMANDANT:,GSMART=:GSMART:,GSMVAR=:GSMVAR:,GSMPOS=:GSMPOS:,GSMRBEZ=:GSMRBEZ:,GSMRFARBE=:GSMRFARBE:,GSMRANT=:GSMRANT:,GSMSTAPELV=:GSMSTAPELV:,GSMSTAPELB=:GSMSTAPELB:,GSMDTEX=:GSMDTEX:,GSMBON=:GSMBON:,GSMRFBBEZ=:GSMRFBBEZ:,GSMABF=:GSMABF:,GSMKGNET=:GSMKGNET:,GSMKGBRUT=:GSMKGBRUT:,GSMDISPOKZ=:GSMDISPOKZ:,GSMRSPZ=:GSMRSPZ:,GSMRKZ=:GSMRKZ:,GSMLIEFNR=:GSMLIEFNR:,GSMVERG=:GSMVERG:,GSMVEART1=:GSMVEART1:,GSMVESATZ1=:GSMVESATZ1:,GSMVEART2=:GSMVEART2:,GSMVESATZ2=:GSMVESATZ2:,GSMFILLER=:GSMFILLER:"
                        + " WHERE  GSMMANDANT=:GSMMANDANT: AND GSMART=:GSMART: AND GSMVAR=:GSMVAR: AND GSMPOS=:GSMPOS:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
    }
}
